package com.huyna.rss.data;

import java.util.ArrayList;
import java.util.List;

import com.huyna.rss.model.Website;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class RssDatabaseHandler extends SQLiteOpenHelper{
    private static final int    DATABASE_VERSION    = 1;
    private static final String DATABASE_NAME       = "RssReader";
    private static final String TABLE_RSS           = "WEBSITES";
    
    private static final String KEY_ID              = "Id";
    private static final String KEY_TITLE           = "Title";
    private static final String KEY_LINK            = "Link";
    private static final String KEY_RSS_LINK        = "RssLink";
    private static final String KEY_DESCRIPTION     = "Description";
    
    public RssDatabaseHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_TABLE = "CREATE TABLE " + TABLE_RSS + "("
                + KEY_ID            + " INTEGER PRIMARY KEY,"
                + KEY_TITLE         + " TEXT,"
                + KEY_LINK          + " TEXT,"
                + KEY_RSS_LINK      + " TEXT,"
                + KEY_DESCRIPTION   + " TEXT)";
        db.execSQL(CREATE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_RSS);
        onCreate(db);
    }
    
    public void insertWebsite(Website site){
        SQLiteDatabase db = getWritableDatabase();
        
        ContentValues values = new ContentValues();
        values.put(KEY_TITLE, site.getTitle());
        values.put(KEY_LINK, site.getLink());
        values.put(KEY_RSS_LINK, site.getRssLink());
        values.put(KEY_DESCRIPTION, site.getDescription());
        if(!isSiteExisted(db, site.getLink())){
            db.insert(TABLE_RSS, null, values);
        }
        else{
            updateWebsite(site);
        }
        db.close();
    }

    public List<Website> selectAllWebsites(){
        List<Website> sites = new ArrayList<Website>();
        SQLiteDatabase db = getReadableDatabase();
        
        String query = "SELECT * FROM "+ TABLE_RSS;
        Cursor cursor = db.rawQuery(query, null);
        if(cursor.getCount() > 0){
            if(cursor.moveToFirst()){
                do{
                    Website site = new Website();
                    site.setId(Integer.parseInt(cursor.getString(0)));
                    site.setTitle(cursor.getString(1));
                    site.setLink(cursor.getString(2));
                    site.setRssLink(cursor.getString(3));
                    site.setDescription(cursor.getString(4));
                    sites.add(site);
                }
                while(cursor.moveToNext());
            }
        }
        cursor.close();
        db.close();
        
        return sites;
    }
    
    public Website getWebsite(int id){
        Website site = new Website();
        SQLiteDatabase db = getReadableDatabase();
        
        String query = "SELECT * FROM " + TABLE_RSS + " WHERE " + KEY_ID + "= "+id;
        Cursor cursor = db.rawQuery(query, null);
        if(cursor.getCount() > 0){
            cursor.moveToFirst();
            
            site.setId(id);
            site.setTitle(cursor.getString(1));
            site.setLink(cursor.getString(2));
            site.setRssLink(cursor.getString(3));
            site.setDescription(cursor.getString(4));
        }
        cursor.close();
        db.close();
        
        return site;
    }
    
    public int deleteWebsite(Website site){
        SQLiteDatabase db = getWritableDatabase();
        int result = db.delete(TABLE_RSS, KEY_ID + "= ?", new String[] {String.valueOf(site.getId())});
        db.close();
        
        return result;
    }
    
    private int updateWebsite(Website site){
        SQLiteDatabase db = getWritableDatabase();
        
        ContentValues values = new ContentValues();
        values.put(KEY_TITLE, site.getTitle());
        values.put(KEY_LINK, site.getLink());
        values.put(KEY_RSS_LINK, site.getRssLink());
        values.put(KEY_DESCRIPTION, site.getDescription());
        
        int result = db.update(TABLE_RSS, values, KEY_RSS_LINK + "= ?", new String[] {String.valueOf(site.getRssLink())});
        db.close();
        
        return result;
    }
    private boolean isSiteExisted(SQLiteDatabase db, String link){
        Cursor cursor = db.rawQuery("SELECT 1 FROM "+ TABLE_RSS
                + "WHERE RssLink='" + link + "'", new String[]{});
        if(cursor.getCount() > 0){
            return true;
        }
        return false;
    }
}
